import json
from control.models import *
from unit_management.models import *
import pandas as pd
from django.db import IntegrityError, transaction
import glob

# 初始化单位词头


def init_unit_prefix():

    super_admin_entity = User.objects.filter(user_account="superadmin").first()
    data_file = "../final_basic_tables/unit_data_sample.json"
    data_content = json.load(open(data_file, "r", encoding="utf-8"))
    prefix_data = data_content["prefix"]
    prefix_entity_list = []
    for prefix_item in prefix_data:
        unit_prefix_entity = UnitPrefix(
            **{
                "prefix_name": prefix_item["prefix_name_ch"],
                "prefix_name_en": prefix_item["prefix_name_en"],
                "prefix_base": prefix_item["base"],
                "prefix_power": prefix_item["power"],
                "prefix_symbol": prefix_item["symbol"],
                "prefix_symbol_ascii": prefix_item["symbol_ascii"],
                "prefix_symbol_html": prefix_item["symbol_html"],
                "prefix_symbol_latex": prefix_item["symbol_latex"],
                "prefix_symbol_unicode": prefix_item["symbol_unicode"],
                "prefix_description": prefix_item["description"],
                "prefix_comment": prefix_item["comment"],
                "create_person": super_admin_entity,
                "modify_person": super_admin_entity,
            }
        )
        prefix_entity_list.append(unit_prefix_entity)
    UnitPrefix.objects.bulk_create(prefix_entity_list)


# 初始化基本量纲
def init_unit_basic_dimension():
    super_admin_entity = User.objects.filter(user_account="superadmin").first()

    data_file = "../final_basic_tables/unit_data_sample.json"
    data_content = json.load(open(data_file, "r", encoding="utf-8"))
    basic_dimension_data = data_content["basic_dimension"]
    basic_dimension_entity_list = []
    for basic_dimension_item in basic_dimension_data:
        basic_dimension_entity = BasicDimension(
            **{
                "basic_dimension_name": basic_dimension_item["sBasicDimFormula"],
                "basic_dimension_name_en": basic_dimension_item["nBasicDimNameEN"],
                "basic_dimension_symbol": basic_dimension_item["sBasicDimSymbol"],
                "sei_id": basic_dimension_item["nPCdataBasicDimID"],
                "basic_dimension_symbol_ascii": basic_dimension_item["sBasicDimAsciiSymbol"],
                "basic_dimension_symbol_html": basic_dimension_item["sBasicDimHtmlSymbol"],
                "basic_dimension_symbol_latex": basic_dimension_item["sBasicDimLatexSymbol"],
                "basic_dimension_symbol_unicode": basic_dimension_item["sBasicDimUnicodeSymbol"],
                "basic_dimension_description": basic_dimension_item["sBasicDimDescription"],
                "basic_dimension_description_en": basic_dimension_item["sBasicDimDescriptionEN"],
                "basic_dimension_comment": basic_dimension_item["sBasicDimComment"],
                "create_person": super_admin_entity,
                "modify_person": super_admin_entity,
            }
        )
        basic_dimension_entity_list.append(basic_dimension_entity)
    BasicDimension.objects.bulk_create(basic_dimension_entity_list)
    print(f"基础量纲初始化完毕，共录入: {len(basic_dimension_entity_list)} 个基础量纲")

# 初始化量纲


def init_unit_dimension():
    # 创建指数符号字典
    superscript_map = {
        '0': '⁰', '1': '¹', '2': '²', '3': '³', '4': '⁴',
        '5': '⁵', '6': '⁶', '7': '⁷', '8': '⁸', '9': '⁹',
        '.': '⋅', '-': '⁻', '·': '·',
    }
    up_index_to_normal = {}
    for key in superscript_map:
        up_index_to_normal[superscript_map[key]] = key

    super_admin_entity = User.objects.filter(user_account="superadmin").first()
    data_file = "../final_basic_tables/unit_data_sample.json"
    data_content = json.load(open(data_file, "r", encoding="utf-8"))
    basic_dimension_data = data_content["basic_dimension"]
    # 获取这个basic_dimension中默认单位对应entity的字典
    basic_dimension_unit_entity_map = {}
    for basic_dimension_item in basic_dimension_data:
        basic_dimension_unit_entity_map[basic_dimension_item["sBasicDimComment"]] = BasicDimension.objects.filter(
            basic_dimension_name=basic_dimension_item["sBasicDimFormula"]
        ).first()

    # 加载excel中的数据
    # 需要修改对应的行和列数量
    matched_files = glob.glob("../final_basic_tables/A080A. PCdata基表-8-量表*.xlsx")
    latest_file = max(matched_files, key=lambda x: x.split("-")[-1]) if matched_files else None
    sheet_name = "量纲表（入库版）"
    df = pd.read_excel(latest_file, sheet_name=sheet_name,
                       header=0, skiprows=[0,2], dtype=str)
    start_row = 0
    num_columns = 10
    selected_data = df.iloc[start_row:, :num_columns].fillna("")
    dimension_list = []
    # print(selected_data)
    # print(selected_data.columns)
    # return
    # 将sDimensionPhysicalSymbol转为正常的单位符号和指数
    def replace_string(s, map1):
        for char in map1:
            s = s.replace(char, map1[char])
        return s

    num_char_list = superscript_map.keys()

    for index, row in selected_data.iterrows():
        dimension_item = {}
        # dimension_item["dimension_name"] = row["sDimensionNameCN"]
        # dimension_item["dimension_name_en"] = row["sDimensionName"]
        dimension_item["sei_id"] = row["nPCdataDimID"]
        dimension_item["dimension_description"] = row["sDimDescription"]
        dimension_item["sDimName"] = row["sDimSymbol"]
        dimension_consistent = {}
        if row["sDimFormula"] == "1":
            dimension_item["consistent"] = {}
            dimension_list.append(dimension_item)
            continue
        # print(row["sDimensionPhysicalSymbol"])
        dimension_content = row["sDimFormula"].split("·")
        for dimension1 in dimension_content:
            unit_symbol = replace_string(dimension1, up_index_to_normal)
            # 获取不在num_char_list中的
            symbol = ""
            num1 = ""
            for char1 in unit_symbol:
                if char1 not in num_char_list:
                    symbol += char1
                else:
                    num1 += char1
            if num1 == "":
                num1 = 1
            else:
                num1 = float(num1)
            # 如果没有，直接认为无量纲，
            if symbol not in basic_dimension_unit_entity_map:
                dimension_consistent = {}
                break
            dimension_consistent[basic_dimension_unit_entity_map[symbol]] = num1
        dimension_item["consistent"] = dimension_consistent
        dimension_list.append(dimension_item)
    # print(dimension_list)
    # return
    # 进行量纲入库
    dimension_entity_list = []
    dimension_map_list = []
    try:
        with transaction.atomic():
            for dimension_item in dimension_list:
                dimension_entity = Dimension(
                    dimension_name = dimension_item["sDimName"],
                    sei_id = dimension_item["sei_id"],
                    dimension_description = dimension_item["dimension_description"],
                    create_person=super_admin_entity,
                    modify_person=super_admin_entity
                )
                dimension_entity_list.append(dimension_entity)
                # 量纲map
                for key in dimension_item["consistent"]:
                    dimension_map_list.append(
                        DimensionMap(
                            basic_dimension=key,
                            dimension=dimension_entity,
                            power=dimension_item["consistent"][key]
                        )
                    )
            Dimension.objects.bulk_create(dimension_entity_list)
            DimensionMap.objects.bulk_create(dimension_map_list)

    except IntegrityError as e:
        print(str(e))
    print(f"量纲初始化完毕，共录入: {len(dimension_entity_list)} 个量纲")

def Unit_Dim_Mapping():
    matched_files = glob.glob("../final_basic_tables/A080A. PCdata基表-8-量表*.xlsx")
    latest_file = max(matched_files, key=lambda x: x.split("-")[-1]) if matched_files else None
    sheet_name = "量表（入库版）"
    df = pd.read_excel(latest_file, sheet_name=sheet_name, skiprows=[0,2], dtype=str)
    df = df.dropna(how='all')

    required_columns = ["sPCdataUnitGroupID", "nPCdataDimID"]
    if not all(col in df.columns for col in required_columns):
        missing_cols = [col for col in required_columns if col not in df.columns]
        raise ValueError(f"Sheet '{sheet_name}' 缺少列: {missing_cols}")
    mapping_df = df[required_columns].drop_duplicates(subset="sPCdataUnitGroupID")
    
    # 检查是否有 sPCdataUnitGroupID 对应多个 nPCdataDimID（冲突情况）
    grouped = df.groupby('sPCdataUnitGroupID')['nPCdataDimID'].nunique()
    conflict_groups = grouped[grouped > 1].index.tolist()

    if conflict_groups:
        conflict_data = df[df['sPCdataUnitGroupID'].isin(conflict_groups)] \
                        .sort_values(['sPCdataUnitGroupID', 'nPCdataDimID'])
        
        print("ERROR: 以下 sPCdataUnitGroupID 对应多个不同的 nPCdataDimID:")
        print(conflict_data.drop_duplicates(
            subset=['sPCdataUnitGroupID', 'nPCdataDimID']
        ).to_string(index=False))

    # 转换为字典 {sPCdataUnitGroupID: nPCdataDimID}
    GroupIdMap = mapping_df.set_index("sPCdataUnitGroupID")["nPCdataDimID"].to_dict()
    
    return GroupIdMap
    
# 初始化单位
def init_unit_unit():
    super_admin_entity = User.objects.filter(user_account="superadmin").first()
    matched_files = glob.glob("../final_basic_tables/A073. PCdata基表-1-单位表*.xlsx")
    latest_file = max(matched_files, key=lambda x: x.split("-")[-1]) if matched_files else None
    sheet_name = "单位表（入库版）"
    df = pd.read_excel(latest_file, sheet_name=sheet_name,
                       header=0, skiprows=[0,2],dtype=str)
    df = df.dropna(how='all')
    start_row = 0
    num_columns = 7
    selected_data = df.iloc[start_row:, :num_columns].fillna("")
    modules = []
    current_module = []
    current_group = None
    # print(selected_data)
    # return
    
    # 将单位根据单位组编码分组
    for index, row in selected_data.iterrows():
        if row["sPCdataUnitGroupID"] != current_group:
            # 如果 current_module 非空，说明已有数据，先存入 modules
            if current_module:
                modules.append(pd.DataFrame(current_module))
                current_module = []  # 重置临时存储
            # 更新当前分组 ID
            current_group = row["sPCdataUnitGroupID"]
        current_module.append(row)
    if current_module:
        modules.append(pd.DataFrame(current_module))

    # 生成单位组对应量纲编号对应字典
    Unit_Dim_mapping = Unit_Dim_Mapping()

    # 对每个module进行入库
    unit_entity_list = []
    for module in modules:
        num = 0
        root_unit_entity = None
        dimension_entity = None
        for index, row in module.iterrows():
            unit_entity = Unit.objects.filter(sei_id=row["nPCdataUnitID"], unit_name_en=row["sUnitNameEN"]).first()
            if unit_entity == None:
                if num == 0:
                    # 第一个是基准单位
                    dimension_entity = Dimension.objects.filter(
                        sei_id=Unit_Dim_mapping[row["sPCdataUnitGroupID"]]).first()
                    if dimension_entity == None:
                        print("error",row["sPCdataUnitGroupID"])
                    unit_entity = Unit(
                        unit_name=row["sUnitName"],
                        unit_name_en=row["sUnitNameEN"],
                        sei_id=row["nPCdataUnitID"],
                        unit_symbol=row["sUnitSymbol"],
                        dimension=dimension_entity,
                        is_show=True,
                        create_person=super_admin_entity,
                        modify_person=super_admin_entity,
                    )
                    root_unit_entity = unit_entity
                    unit_entity_list.append(unit_entity)
                else:
                    unit_entity = Unit(
                        unit_name=row["sUnitName"],
                        unit_name_en=row["sUnitNameEN"],
                        sei_id=row["nPCdataUnitID"],
                        unit_symbol=row["sUnitSymbol"],
                        dimension=dimension_entity,
                        is_show=True,
                        root_unit=root_unit_entity,
                        conversion_factor=float(row["nUnitFactor"].replace("\u200b", "")),
                        conversion_offset=float(row["nUnitOffset"]),
                        create_person=super_admin_entity,
                        modify_person=super_admin_entity,
                    )
                    unit_entity_list.append(unit_entity)
                num += 1
    try:
        with transaction.atomic():
            Unit.objects.bulk_create(unit_entity_list)
            # raise Exception("test")
    except IntegrityError as e:
        print(str(e))
    print(f"单位初始化完毕，共录入: {len(unit_entity_list)} 个单位")

# 初始化量
def init_unit_quantity():

    super_admin_entity = User.objects.filter(user_account="superadmin").first()

    matched_files = glob.glob("../final_basic_tables/A080A. PCdata基表-8-量表*.xlsx")
    latest_file = max(matched_files, key=lambda x: x.split("-")[-1]) if matched_files else None
    sheet_name = "量表（入库版）"
    df = pd.read_excel(latest_file, sheet_name=sheet_name,
                       header=0, skiprows=[0,2], dtype=str)
    df = df.dropna(subset=['nPCdataQuantityID', 'sQuantityName'], how='all')
    start_row = 0
    num_columns = 13
    selected_data = df.iloc[start_row:, :num_columns]
    modules = []
    current_module = []
    current_group = None
    # print(len(selected_data))
    # return
    # num_set = set()
    for index, row in selected_data.iterrows():
        if row["sPCdataUnitGroupID"] != current_group:
            # 如果 current_module 非空，说明已有数据，先存入 modules
            if current_module:
                modules.append(pd.DataFrame(current_module))
                current_module = []  # 重置临时存储
            # 更新当前分组 ID
            current_group = row["sPCdataUnitGroupID"]
        current_module.append(row)
    if current_module:
        modules.append(pd.DataFrame(current_module))

    # length = 0
    # for module in modules:
    #     length += len(module)
    # print(length)
    # return
    quantity_entity_list = []
    quantity_unit_map_list = []
    quantity_num = 0
    for module in modules:
        num = 0
        quantity_entity = None
        dimension_entity = None
        quantity_num += 1
        # print("quantity_num",quantity_num)
        # if(quantity_num==161):
        #     print("quantity_content",module)
        for index, row in module.iterrows():
            # if num == 0:
                dimension_entity = Dimension.objects.filter(
                    sei_id=row["nPCdataDimID"]).first()
                if dimension_entity == None:
                    print("error1", row["nPCdataDimID"], index)
                    return
                quantity_entity = Quantity(
                    quantity_name=row["sQuantityName"],
                    quantity_name_en=row["sQuantityNameEN"],
                    quantity_abbreviation=row["sQuantityAbb"],
                    quantity_symbol = row["sQuantitySymbol"],
                    sei_id=row["nPCdataQuantityID"],
                    dimension=dimension_entity,
                    quantity_type="NORMAL",
                    quantity_description = row["sQuantityDescription"],
                    
                    create_person=super_admin_entity,
                    modify_person=super_admin_entity
                )
                quantity_entity_list.append(quantity_entity)
                
                unit_entity = Unit.objects.filter(dimension=dimension_entity).first()
                quantity_unit_map_list.append(
                    QuantityUnitMap(
                        quantity=quantity_entity,
                        unit=unit_entity
                    )
                )
            # else:
            #     dimension_entity = Dimension.objects.filter(
            #         sei_id=row["nPCdataDimID"]).first()
            #     if dimension_entity == None:
            #         print("error2", row["nPCdataDimID"], index)
            #         return
            #     unit_entity = Unit.objects.filter(dimension=dimension_entity).first()
            #     quantity_unit_map_list.append(
            #         QuantityUnitMap(
            #             quantity=quantity_entity,
            #             unit=unit_entity
            #         )
            #     )
            # num += 1
    try:
        with transaction.atomic():
            Quantity.objects.bulk_create(quantity_entity_list)
            QuantityUnitMap.objects.bulk_create(quantity_unit_map_list)
            # raise Exception("test")
    except IntegrityError as e:
        print(str(e))


def init_other_unit():
    super_admin_entity = User.objects.filter(user_account="superadmin").first()
    # 初始化一个叫做无量纲的量
    quantity_dimensionless_entity = Quantity.objects.filter(
        quantity_name="无量纲").first()
    if quantity_dimensionless_entity is None:
        quantity_dimensionless_entity = Quantity.objects.create(
            quantity_name="无量纲",
            quantity_name_en="Dimensionless",
            sei_id="Dimensionless",
            dimension=Dimension.objects.filter(dimension_name="D_Dimensionless").first(),
            quantity_description="无量纲",
            create_person=super_admin_entity,
            modify_person=super_admin_entity
        )

    # 初始化一个叫做未找到的量的量
    quantity_miss_entity = Quantity.objects.filter(
        quantity_name="未找到的量").first()
    if quantity_miss_entity is None:
        quantity_miss_entity = Quantity.objects.create(
            quantity_name="未找到的量",
            quantity_name_en="Miss Quantity",
            sei_id="Miss Quantity",
            dimension=Dimension.objects.filter(dimension_name="Unknown").first(),
            quantity_description="创建量叫做未找到，标识物性绑定时没找到这个量",
            create_person=super_admin_entity,
            modify_person=super_admin_entity
        )

    # 初始化一个叫做纯度的量样本纯度使用:样本纯度
    quantity_purity_entity = Quantity.objects.filter(
        quantity_name="样本纯度").first()
    if quantity_purity_entity is None:
        quantity_purity_entity = Quantity.objects.create(
            quantity_name="样本纯度",
            quantity_name_en="Sample Purity",
            sei_id="Sample Purity",
            dimension=Dimension.objects.filter(dimension_name="D_Dimensionless").first(),
            quantity_description="样本纯度",
            create_person=super_admin_entity,
            modify_person=super_admin_entity
        )
        # 构建样本纯度的单位集合
        unit_percent_entity = Unit.objects.filter(
            unit_symbol="%"
        ).first()
        if unit_percent_entity is not None:
            QuantityUnitMap.objects.create(
                quantity=quantity_purity_entity,
                unit=unit_percent_entity
            )
    print(f"量表初始化完毕，共录入: {Quantity.objects.count()} 个量")

# 初始化所有单位内容
def init_unit_content():
    init_unit_prefix()
    init_unit_basic_dimension()
    init_unit_dimension()
    init_unit_unit()
    init_unit_quantity()
    init_other_unit()
